﻿using System;
using System.Data;
using System.Data.SQLite;

class Program
{
    static void Main()
    {
        // 创建 SQLite 连接
        SQLiteConnection connection = new SQLiteConnection("Data Source=students.db");

        try
        {
            connection.Open();

            // 学生信息录入
            AddStudent(connection);
            // 查询学生信息
            QueryStudent(connection);
            // 修改学生信息
            UpdateStudent(connection);
            // 删除学生信息
            DeleteStudent(connection);
            // 学生信息列表展示
            ListStudents(connection);
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
        finally
        {
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
        }
    }

    static void AddStudent(SQLiteConnection connection)
    {
        Console.Write("请输入学生姓名: ");
        string name = Console.ReadLine();

        Console.Write("请输入学生学号: ");
        string studentId = Console.ReadLine();

        Console.Write("请输入专业: ");
        string major = Console.ReadLine();

        string query = "INSERT INTO students (name, student_id, major) VALUES ('" + name + "', '" + studentId + "', '" + major + "')";
        using (SQLiteCommand command = new SQLiteCommand(query, connection))
        {
            command.ExecuteNonQuery();
            Console.WriteLine("学生信息添加成功！");
        }
    }

    static void QueryStudent(SQLiteConnection connection)
    {
        Console.Write("请输入要查询的学生学号或姓名: ");
        string searchTerm = Console.ReadLine();

        string query = "SELECT * FROM students WHERE name LIKE '%" + searchTerm + "%' OR student_id LIKE '%" + searchTerm + "%'";
        using (SQLiteCommand command = new SQLiteCommand(query, connection))
        {
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine($"姓名: {reader["name"]}, 学号: {reader["student_id"]}, 专业: {reader["major"]}");
            }
            reader.Close();
        }
    }

    static void UpdateStudent(SQLiteConnection connection)
    {
        Console.Write("请输入要修改的学生学号: ");
        string studentId = Console.ReadLine();

        string query = "SELECT * FROM students WHERE student_id = '" + studentId + "'";
        using (SQLiteCommand command = new SQLiteCommand(query, connection))
        {
            SQLiteDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                Console.Write("请输入新的学生姓名: ");
                string newName = Console.ReadLine();

                Console.Write("请输入新的专业: ");
                string newMajor = Console.ReadLine();

                string updateQuery = "UPDATE students SET name = '" + newName + "', major = '" + newMajor + "' WHERE student_id = '" + studentId + "'";
                using (SQLiteCommand updateCommand = new SQLiteCommand(updateQuery, connection))
                {
                    updateCommand.ExecuteNonQuery();
                    Console.WriteLine("学生信息修改成功！");
                }
            }
            else
            {
                Console.WriteLine("未找到该学生记录！");
            }
            reader.Close();
        }
    }

    static void DeleteStudent(SQLiteConnection connection)
    {
        Console.Write("请输入要删除的学生学号: ");
        string studentId = Console.ReadLine();

        string query = "DELETE FROM students WHERE student_id = '" + studentId + "'";
        using (SQLiteCommand command = new SQLiteCommand(query, connection))
        {
            command.ExecuteNonQuery();
            Console.WriteLine("学生信息删除成功！");
        }
    }

    static void ListStudents(SQLiteConnection connection)
    {
        string query = "SELECT * FROM students";
        using (SQLiteCommand command = new SQLiteCommand(query, connection))
        {
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine($"姓名: {reader["name"]}, 学号: {reader["student_id"]}, 专业: {reader["major"]}");
            }
            reader.Close();
        }
    }
}